"""
数据库操作模块
包含数据库连接、SQL执行和JSON生成功能
"""
import json
import os
from datetime import datetime
from config import Config

# 数据库连接库
try:
    import mysql.connector
    from mysql.connector import Error
    MYSQL_AVAILABLE = True
except ImportError:
    MYSQL_AVAILABLE = False


def get_database_connection():
    """获取数据库连接"""
    if not MYSQL_AVAILABLE:
        return None, "MySQL连接库未安装，请运行: pip install mysql-connector-python"
    
    try:
        connection = mysql.connector.connect(
            host=Config.MYSQL_HOST,
            port=Config.MYSQL_PORT,
            user=Config.MYSQL_USERNAME,
            password=Config.MYSQL_PASSWORD,
            database=Config.MYSQL_DATABASE
        )
        return connection, None
    except Error as e:
        return None, f"数据库连接失败: {e}"


def execute_sql_and_generate_json(sql_query, upload_folder):
    """执行SQL查询并生成JSON文件"""
    connection, error = get_database_connection()
    if error:
        return None, error, None
    
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute(sql_query)
        results = cursor.fetchall()
        
        if not results:
            return None, "查询结果为空", None
        
        # 转换为trans.json格式
        formatted_data = []
        for row in results:
            # 构建图片URL，假设material_file_key是文件路径或标识符
            image_url = f"https://your-domain.com/images/{row.get('material_file_key', '')}"
            
            formatted_item = {
                "image": {
                    "id": str(row.get('id', '')),
                    "url": image_url,
                    "point_type": str(row.get('point_type', '')),
                    "point_type_name": str(row.get('point_type_name', '')),
                    "point_code": str(row.get('point_code', '')),
                    "point_name": str(row.get('point_name', '')),
                    "material_file_key": str(row.get('material_file_key', '')),
                    "material_file_name": str(row.get('material_file_name', ''))
                }
            }
            formatted_data.append(formatted_item)
        
        # 生成文件名
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"trans-{timestamp}.json"
        filepath = os.path.join(upload_folder, filename)
        
        # 保存JSON文件
        with open(filepath, 'w', encoding='utf-8') as f:
            json.dump(formatted_data, f, ensure_ascii=False, indent=2)
        
        return formatted_data, None, filename
        
    except Error as e:
        return None, f"SQL执行失败: {e}", None
    except Exception as e:
        return None, f"处理数据时出错: {e}", None
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
